{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from visualization_service import VisualizationService\n",
    "from config import ChromaConfig\n",
    "from chroma_manager import ChromaManager"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "chroma_config = ChromaConfig(\n",
    "    host=\"localhost\",\n",
    "    ollama_base_url=\"http://localhost:11434\",\n",
    "    embedding_model=\"viosay/conan-embedding-v1\"\n",
    ")\n",
    "chroma = ChromaManager(chroma_config)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "viz_service = VisualizationService(\n",
    "    chroma_manager=chroma,\n",
    "    model_service_type=\"ollama\",\n",
    "    base_url=\"http://localhost:11434\",\n",
    "    model_name=\"qwen2.5:3b\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "question = \"统计去年全院各个科室不含药品费的门诊总收入是多少, 用饼图展示\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Add of existing embedding ID: ProDate_危急值处理主题\n",
      "Add of existing embedding ID: PHUnit_药房科室库存日报\n",
      "Add of existing embedding ID: AdmCode_住院证登记表\n",
      "Add of existing embedding ID: AdmDep_住院证登记表\n",
      "Add of existing embedding ID: SFFlag_住院证登记表\n",
      "Add of existing embedding ID: Receiver_建卡费主题\n",
      "Add of existing embedding ID: SFFlag_药库业务明细表\n",
      "Add of existing embedding ID: PHUnit_药库业务明细表\n",
      "Add of existing embedding ID: PHUnit_药库业务明细表\n",
      "Add of existing embedding ID: userDr_药库业务明细表\n",
      "Add of existing embedding ID: AdmNo_病理切片主题\n",
      "Add of existing embedding ID: AdmType_病理切片主题\n",
      "Add of existing embedding ID: PatSex_病理切片主题\n",
      "Add of existing embedding ID: Age_病理切片主题\n",
      "Add of existing embedding ID: ReqDate_病理切片主题\n",
      "Add of existing embedding ID: SFFlag_病理切片主题\n",
      "Add of existing embedding ID: StatisticDate_病理切片主题\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Prompt Content: \n",
      "你是一位数据分析助手，帮助用户生成查询接口的参数。  \n",
      "用户问题是：“统计去年全院各个科室不含药品费的门诊总收入是多少, 用饼图展示”。  \n",
      "\n",
      "请严格按照以下要求生成输出：  \n",
      "1. 只输出 JSON，禁止附加任何其他内容（如文字说明）。  \n",
      "2. 输出必须是有效的 JSON 格式。  \n",
      "3. 输出中指标和维度必须基于相同的主题，且指标和维度要能通过主题关联上。\n",
      "4. 如果多个指标或维度符合语义，优先选择包含完整语义信息的项。\n",
      "5. 如果用户的问题描述缺少必要信息（如时间范围、指标或维度），输出包含“无法解析”的错误信息。\n",
      "\n",
      "附加信息：\n",
      "1. 当前时间是：2025-01-08 17:02:47。\n",
      "2. 用户问题中“按照xxx时间或日期统计”一般指的是**统计口径**，应识别为 `basis` 字段；“按xxx展示”指的是**维度**，应识别为 `dims` 字段。\n",
      "3. 时间范围说明：\n",
      "    “去年”指的是上一自然年的时间范围，例如当前时间是 2024 年，则“去年”指 2023-01-01 到 2023-12-31。\n",
      "    “上个月”指的是上一自然月，例如当前时间是 2024-06-15，则“上个月”指 2024-05-01 到 2024-05-31。\n",
      "    “本月”指的是当前自然月，例如当前时间是 2024-06-15，则“本月”指 2024-06-01 到 2024-06-30。\n",
      "\n",
      "后台系统中，你可以使用的指标和维度如下：  \n",
      "(指标描述中括号内的是指标的统计口径)\n",
      "| 指标代码 | 指标描述 | 关联主题 | 计算公式 |\n",
      "|---|---|---|---|\n",
      "| HQMSZ00103 | 医疗收入(三级公立中医) | 绩效填报主题 | - |\n",
      "| HQMSZ00098 | 医疗服务收入(三级公立中医) | 绩效填报主题 | - |\n",
      "| S0010038 | 门诊药品总收入(计费日期) | 收入主题 | - |\n",
      "| S0010022 | 全院药品总收入(计费日期) | 收入主题 | - |\n",
      "| S0010039 | 门诊药品总收入(结算日期) | 收入主题 | - |\n",
      "| S0010041 | 门诊药品总收入(核算日期) | 收入主题 | - |\n",
      "| S0010023 | 全院药品总收入(结算日期) | 收入主题 | - |\n",
      "| S0010025 | 全院药品总收入(核算日期) | 收入主题 | - |\n",
      "| HQMSZ00097 | 33.医疗服务收入（不含药品、耗材、检查检验收入）占医疗收入比例▲(三级公立中医) | - | {HQMSZ00098/HQMSZ00103} |\n",
      "| S0010146 | 门诊中成药总收入(计费日期) | 收入主题 | - |\n",
      "| S0010040 | 门诊药品总收入(交账日期) | 收入主题 | - |\n",
      "| S0010130 | 全院中成药总收入(计费日期) | 收入主题 | - |\n",
      "\n",
      "\n",
      "| 维度代码 | 维度描述 | 所属主题 |\n",
      "|---|---|---|\n",
      "| OpCat | 门诊大类 | 收入主题 |\n",
      "| OpSubCat | 门诊子类 | 收入主题 |\n",
      "| ResDepHos | 开单科室院区 | 收入主题 |\n",
      "| OeoriItem | 医嘱流水主键 | 收入主题 |\n",
      "| InCat | 住院大类 | 收入主题 |\n",
      "| OperFlag | 手术费标识 | 收入主题 |\n",
      "| MRCat | 病案大类 | 收入主题 |\n",
      "| TariCat | 收费项大类 | 收入主题 |\n",
      "| ResDocMG | 开单医生诊疗组 | 收入主题 |\n",
      "| TecCat | 核算大类 | 收入主题 |\n",
      "\n",
      "\n",
      "根据用户的问题，生成以下 JSON 参数：  \n",
      "1. 时间范围（startDate 和 endDate）：推断问题中提到的时间范围。  \n",
      "2. kpis：选择合适的指标代码，格式为“指标代码 as 别名”。  \n",
      "3. dims：选择合适的维度代码，格式为“维度代码 -> DESC as 别名”。  \n",
      "4. basis：统计口径。  \n",
      "\n",
      "示例：  \n",
      "输入问题：“统计上个月的住院量按病区分类”  \n",
      "输出：  \n",
      "```json\n",
      "\n",
      "{\n",
      "    \"startDate\": \"2023-11-01\",\n",
      "    \"endDate\": \"2023-11-30\",\n",
      "    \"kpis\": \"S0010002 as 住院量\",\n",
      "    \"dims\": \"D001 -> DESC as 病区\",\n",
      "    \"basis\": \"挂号日期\"\n",
      "}\n",
      "\n",
      "Params Prompt: [{'role': 'system', 'content': '你是一位数据分析助手，帮助用户生成查询接口的参数。'}, {'role': 'user', 'content': '\\n你是一位数据分析助手，帮助用户生成查询接口的参数。  \\n用户问题是：“统计去年全院各个科室不含药品费的门诊总收入是多少, 用饼图展示”。  \\n\\n请严格按照以下要求生成输出：  \\n1. 只输出 JSON，禁止附加任何其他内容（如文字说明）。  \\n2. 输出必须是有效的 JSON 格式。  \\n3. 输出中指标和维度必须基于相同的主题，且指标和维度要能通过主题关联上。\\n4. 如果多个指标或维度符合语义，优先选择包含完整语义信息的项。\\n5. 如果用户的问题描述缺少必要信息（如时间范围、指标或维度），输出包含“无法解析”的错误信息。\\n\\n附加信息：\\n1. 当前时间是：2025-01-08 17:02:47。\\n2. 用户问题中“按照xxx时间或日期统计”一般指的是**统计口径**，应识别为 `basis` 字段；“按xxx展示”指的是**维度**，应识别为 `dims` 字段。\\n3. 时间范围说明：\\n    “去年”指的是上一自然年的时间范围，例如当前时间是 2024 年，则“去年”指 2023-01-01 到 2023-12-31。\\n    “上个月”指的是上一自然月，例如当前时间是 2024-06-15，则“上个月”指 2024-05-01 到 2024-05-31。\\n    “本月”指的是当前自然月，例如当前时间是 2024-06-15，则“本月”指 2024-06-01 到 2024-06-30。\\n\\n后台系统中，你可以使用的指标和维度如下：  \\n(指标描述中括号内的是指标的统计口径)\\n| 指标代码 | 指标描述 | 关联主题 | 计算公式 |\\n|---|---|---|---|\\n| HQMSZ00103 | 医疗收入(三级公立中医) | 绩效填报主题 | - |\\n| HQMSZ00098 | 医疗服务收入(三级公立中医) | 绩效填报主题 | - |\\n| S0010038 | 门诊药品总收入(计费日期) | 收入主题 | - |\\n| S0010022 | 全院药品总收入(计费日期) | 收入主题 | - |\\n| S0010039 | 门诊药品总收入(结算日期) | 收入主题 | - |\\n| S0010041 | 门诊药品总收入(核算日期) | 收入主题 | - |\\n| S0010023 | 全院药品总收入(结算日期) | 收入主题 | - |\\n| S0010025 | 全院药品总收入(核算日期) | 收入主题 | - |\\n| HQMSZ00097 | 33.医疗服务收入（不含药品、耗材、检查检验收入）占医疗收入比例▲(三级公立中医) | - | {HQMSZ00098/HQMSZ00103} |\\n| S0010146 | 门诊中成药总收入(计费日期) | 收入主题 | - |\\n| S0010040 | 门诊药品总收入(交账日期) | 收入主题 | - |\\n| S0010130 | 全院中成药总收入(计费日期) | 收入主题 | - |\\n\\n\\n| 维度代码 | 维度描述 | 所属主题 |\\n|---|---|---|\\n| OpCat | 门诊大类 | 收入主题 |\\n| OpSubCat | 门诊子类 | 收入主题 |\\n| ResDepHos | 开单科室院区 | 收入主题 |\\n| OeoriItem | 医嘱流水主键 | 收入主题 |\\n| InCat | 住院大类 | 收入主题 |\\n| OperFlag | 手术费标识 | 收入主题 |\\n| MRCat | 病案大类 | 收入主题 |\\n| TariCat | 收费项大类 | 收入主题 |\\n| ResDocMG | 开单医生诊疗组 | 收入主题 |\\n| TecCat | 核算大类 | 收入主题 |\\n\\n\\n根据用户的问题，生成以下 JSON 参数：  \\n1. 时间范围（startDate 和 endDate）：推断问题中提到的时间范围。  \\n2. kpis：选择合适的指标代码，格式为“指标代码 as 别名”。  \\n3. dims：选择合适的维度代码，格式为“维度代码 -> DESC as 别名”。  \\n4. basis：统计口径。  \\n\\n示例：  \\n输入问题：“统计上个月的住院量按病区分类”  \\n输出：  \\n```json\\n\\n{\\n    \"startDate\": \"2023-11-01\",\\n    \"endDate\": \"2023-11-30\",\\n    \"kpis\": \"S0010002 as 住院量\",\\n    \"dims\": \"D001 -> DESC as 病区\",\\n    \"basis\": \"挂号日期\"\\n}\\n'}]\n",
      "LLM Response: ```json\n",
      "{\n",
      "    \"startDate\": \"2023-01-01\",\n",
      "    \"endDate\": \"2023-12-31\",\n",
      "    \"kpis\": \"HQMSZ00097 as 门诊总收入(不含药品)\",\n",
      "    \"dims\": \"ResDepHos -> DESC as 科室\",\n",
      "    \"basis\": \"计费日期\"\n",
      "}\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "params = viz_service.generate_kpi_params(question)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{\n",
      "    \"startDate\": \"2023-01-01\",\n",
      "    \"endDate\": \"2023-12-31\",\n",
      "    \"kpis\": \"HQMSZ00097 as 门诊总收入(不含药品)\",\n",
      "    \"dims\": \"ResDepHos -> DESC as 科室\",\n",
      "    \"basis\": \"计费日期\"\n",
      "}\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(params)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>部门</th>\n",
       "      <th>销售额</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>销售</td>\n",
       "      <td>2153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>市场</td>\n",
       "      <td>1623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>技术</td>\n",
       "      <td>4803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>财务</td>\n",
       "      <td>3041</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>行政</td>\n",
       "      <td>1260</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   部门   销售额\n",
       "0  销售  2153\n",
       "1  市场  1623\n",
       "2  技术  4803\n",
       "3  财务  3041\n",
       "4  行政  1260"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "data = {\n",
    "    \"部门\": [\"销售\", \"市场\", \"技术\", \"财务\", \"行政\"],\n",
    "    \"销售额\": np.random.randint(1000, 5000, size=5)\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Plotly Prompt: [{'role': 'system', 'content': 'The following is a pandas DataFrame that contains the results of the query that answers the question the user asked: \\'统计去年全院各个科室不含药品费的门诊总收入是多少, 用饼图展示\\'\\n\\nThe DataFrame was produced using this params: {\\n    \"startDate\": \"2023-01-01\",\\n    \"endDate\": \"2023-12-31\",\\n    \"kpis\": \"HQMSZ00097 as 门诊总收入(不含药品)\",\\n    \"dims\": \"ResDepHos -> DESC as 科室\",\\n    \"basis\": \"计算日期\"\\n}\\n\\n\\nThe following is information about the resulting pandas DataFrame \\'df\\': \\n部门     object\\n销售额     int32\\ndtype: object'}, {'role': 'user', 'content': \"Can you generate the Python plotly code to chart the results of the dataframe? Assume the data is in a pandas dataframe called 'df'. If there is only one value in the dataframe, use an Indicator. Respond with only Python code. Do not answer with any explanations -- just the code.\"}]\n",
      "LLM Response: ```python\n",
      "from plotly import express as px\n",
      "\n",
      "indicator = px.indicator.gauge_caution()\n",
      "fig = px.figure({\"data\": [df[\"销售额\"].sum()], \"layout\": {\"title\": \"门诊总收入(不含药品)\"}}, height=450, width=700)\n",
      "fig.update_layout(\n",
      "    margin=dict(l=0, r=0, t=60, b=60),\n",
      "    plot_bgcolor='rgba(0,0,0,0)',\n",
      "    indicator=[dict(value=df[\"销售额\"].sum(), title=f\"门诊总收入(不含药品): {df['销售额'].sum()}元\", mode=\"gauge+number\", gauge=dict(axis=dict(range=[None, df['销售额'].sum()])))])\n",
      "fig.show()\n",
      "```\n"
     ]
    }
   ],
   "source": [
    "code = viz_service.generate_plot_code(question, params, df.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "from plotly import express as px\n",
      "\n",
      "indicator = px.indicator.gauge_caution()\n",
      "fig = px.figure({\"data\": [df[\"销售额\"].sum()], \"layout\": {\"title\": \"门诊总收入(不含药品)\"}}, height=450, width=700)\n",
      "fig.update_layout(\n",
      "    margin=dict(l=0, r=0, t=60, b=60),\n",
      "    plot_bgcolor='rgba(0,0,0,0)',\n",
      "    indicator=[dict(value=df[\"销售额\"].sum(), title=f\"门诊总收入(不含药品): {df['销售额'].sum()}元\", mode=\"gauge+number\", gauge=dict(axis=dict(range=[None, df['销售额'].sum()])))])\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(code)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "langflow",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
